package holo.Utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import util.YtJSONObject;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @author Malegod_xiaofei
 * @create 2022-06-10-11:55
 */
public class Update_Holo_2018 {
    //定义SQL 链接 预编译器
    private static DataSource dataSource = null;
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;

    public static void main(String[] args) throws Exception {
        String[] month = {"201801", "201802", "201803", "201804", "201805", "201806", "201807", "201808", "201809", "201810", "201811", "201812"};
        Map<String, JSONObject> check_region_id = new HashMap<>();

        check_region_id.put("120225", new YtJSONObject().put("new_region_id", "120119").put("province", "天津市").put("city", "天津市").put("district", "蓟州区").getHome());
        check_region_id.put("130428", new YtJSONObject().put("new_region_id", "130407").put("province", "河北省").put("city", "邯郸市").put("district", "肥乡县").getHome());
        check_region_id.put("130429", new YtJSONObject().put("new_region_id", "130408").put("province", "河北省").put("city", "邯郸市").put("district", "永年区").getHome());
        check_region_id.put("130729", new YtJSONObject().put("new_region_id", "130708").put("province", "河北省").put("city", "张家口市").put("district", "万全区").getHome());
        check_region_id.put("130823", new YtJSONObject().put("new_region_id", "130881").put("province", "河北省").put("city", "承德市").put("district", "平泉县").getHome());
        check_region_id.put("131181", new YtJSONObject().put("new_region_id", "131103").put("province", "河北省").put("city", "衡水市").put("district", "冀州区").getHome());
        check_region_id.put("139001", new YtJSONObject().put("new_region_id", "130682").put("province", "河北省").put("city", "保定市").put("district", "定州市").getHome());
        check_region_id.put("139002", new YtJSONObject().put("new_region_id", "130181").put("province", "河北省").put("city", "石家庄市").put("district", "辛集市").getHome());
        check_region_id.put("140202", new YtJSONObject().put("new_region_id", "140213").put("province", "山西省").put("city", "大同市").put("district", "平城区").getHome());
        check_region_id.put("140203", new YtJSONObject().put("new_region_id", "140214").put("province", "山西省").put("city", "大同市").put("district", "矿区").getHome());
        check_region_id.put("140211", new YtJSONObject().put("new_region_id", "140214").put("province", "山西省").put("city", "大同市").put("district", "云冈区").getHome());
        check_region_id.put("140227", new YtJSONObject().put("new_region_id", "140215").put("province", "山西省").put("city", "大同市").put("district", "大同县").getHome());
        check_region_id.put("140402", new YtJSONObject().put("new_region_id", "140403").put("province", "山西省").put("city", "长治市").put("district", "潞州区").getHome());
        check_region_id.put("140411", new YtJSONObject().put("new_region_id", "140403").put("province", "山西省").put("city", "长治市").put("district", "潞州区").getHome());
        check_region_id.put("140624", new YtJSONObject().put("new_region_id", "140681").put("province", "山西省").put("city", "朔州市").put("district", "怀仁市").getHome());
        check_region_id.put("232723", new YtJSONObject().put("new_region_id", "232701").put("province", "黑龙江省").put("city", "大兴安岭地区").put("district", "漠河市").getHome());
        check_region_id.put("310230", new YtJSONObject().put("new_region_id", "310151").put("province", "上海市").put("city", "上海市").put("district", "崇明区").getHome());
        check_region_id.put("320504", new YtJSONObject().put("new_region_id", "320508").put("province", "江苏省").put("city", "苏州市").put("district", "姑苏区").getHome());
        check_region_id.put("320621", new YtJSONObject().put("new_region_id", "320685").put("province", "江苏省").put("city", "南通市").put("district", "海安县").getHome());
        check_region_id.put("330185", new YtJSONObject().put("new_region_id", "330112").put("province", "浙江省").put("city", "杭州市").put("district", "临安市").getHome());
        check_region_id.put("330283", new YtJSONObject().put("new_region_id", "330213").put("province", "浙江省").put("city", "宁波市").put("district", "奉化市").getHome());
        check_region_id.put("331021", new YtJSONObject().put("new_region_id", "331083").put("province", "浙江省").put("city", "台州市").put("district", "玉环县").getHome());
        check_region_id.put("340824", new YtJSONObject().put("new_region_id", "340882").put("province", "安徽省").put("city", "安庆市").put("district", "潜山市").getHome());
        check_region_id.put("350182", new YtJSONObject().put("new_region_id", "350112").put("province", "福建省").put("city", "福州市").put("district", "长乐市").getHome());
        check_region_id.put("350822", new YtJSONObject().put("new_region_id", "350429").put("province", "福建省").put("city", "三明市").put("district", "泰宁县").getHome());
        check_region_id.put("360421", new YtJSONObject().put("new_region_id", "360404").put("province", "江西省").put("city", "九江市").put("district", "柴桑区").getHome());
        check_region_id.put("360427", new YtJSONObject().put("new_region_id", "360603").put("province", "江西省").put("city", "鹰潭市").put("district", "余江区").getHome());
        check_region_id.put("360622", new YtJSONObject().put("new_region_id", "360603").put("province", "江西省").put("city", "鹰潭市").put("district", "余江区").getHome());
        check_region_id.put("360721", new YtJSONObject().put("new_region_id", "360704").put("province", "江西省").put("city", "赣州市").put("district", "赣县区").getHome());
        check_region_id.put("361029", new YtJSONObject().put("new_region_id", "361003").put("province", "江西省").put("city", "抚州市").put("district", "东乡区").getHome());
        check_region_id.put("370181", new YtJSONObject().put("new_region_id", "370114").put("province", "山东省").put("city", "济南市").put("district", "章丘市").getHome());
        check_region_id.put("370282", new YtJSONObject().put("new_region_id", "370215").put("province", "山东省").put("city", "青岛市").put("district", "即墨区").getHome());
        check_region_id.put("371200", new YtJSONObject().put("new_region_id", "371202").put("province", "山东省").put("city", "济南市").put("district", "莱芜区").getHome());
        check_region_id.put("371626", new YtJSONObject().put("new_region_id", "371681").put("province", "山东省").put("city", "滨州市").put("district", "邹平市").getHome());
        check_region_id.put("411023", new YtJSONObject().put("new_region_id", "411003").put("province", "河南省").put("city", "许昌市").put("district", "许昌县").getHome());
        check_region_id.put("420821", new YtJSONObject().put("new_region_id", "420882").put("province", "湖北省").put("city", "荆门市").put("district", "京山市").getHome());
        check_region_id.put("430124", new YtJSONObject().put("new_region_id", "430182").put("province", "湖南省").put("city", "长沙市").put("district", "宁乡市").getHome());
        check_region_id.put("430221", new YtJSONObject().put("new_region_id", "430212").put("province", "湖南省").put("city", "株洲市").put("district", "渌口区").getHome());
        check_region_id.put("440302", new YtJSONObject().put("new_region_id", "440308").put("province", "广东省").put("city", "深圳市").put("district", "盐田区").getHome());
        check_region_id.put("450331", new YtJSONObject().put("new_region_id", "450381").put("province", "广西壮族自治区").put("city", "桂林市").put("district", "荔浦市").getHome());
        check_region_id.put("451281", new YtJSONObject().put("new_region_id", "451203").put("province", "广西壮族自治区").put("city", "河池市").put("district", "宜州市").getHome());
        check_region_id.put("500156", new YtJSONObject().put("new_region_id", "500232").put("province", "重庆市").put("city", "重庆市").put("district", "武隆区").getHome());
        check_region_id.put("500228", new YtJSONObject().put("new_region_id", "500155").put("province", "重庆市").put("city", "重庆市").put("district", "梁平县").getHome());
        check_region_id.put("510124", new YtJSONObject().put("new_region_id", "510117").put("province", "四川省").put("city", "成都市").put("district", "郫县").getHome());
        check_region_id.put("510626", new YtJSONObject().put("new_region_id", "510604").put("province", "四川省").put("city", "德阳市").put("district", "罗江区").getHome());
        check_region_id.put("511028", new YtJSONObject().put("new_region_id", "511083").put("province", "四川省").put("city", "内江市").put("district", "隆昌县").getHome());
        check_region_id.put("511521", new YtJSONObject().put("new_region_id", "511504").put("province", "四川省").put("city", "宜宾市").put("district", "叙州区").getHome());
        check_region_id.put("520222", new YtJSONObject().put("new_region_id", "520281").put("province", "贵州省").put("city", "六盘水市").put("district", "盘州市").getHome());
        check_region_id.put("520321", new YtJSONObject().put("new_region_id", "520304").put("province", "贵州省").put("city", "遵义市").put("district", "播州区").getHome());
        check_region_id.put("522322", new YtJSONObject().put("new_region_id", "522302").put("province", "贵州省").put("city", "黔西南布依族苗族自治州").put("district", "兴仁市").getHome());
        check_region_id.put("530122", new YtJSONObject().put("new_region_id", "530115").put("province", "云南省").put("city", "昆明市").put("district", "晋宁区").getHome());
        check_region_id.put("530321", new YtJSONObject().put("new_region_id", "530304").put("province", "云南省").put("city", "曲靖市").put("district", "马龙县").getHome());
        check_region_id.put("530328", new YtJSONObject().put("new_region_id", "530303").put("province", "云南省").put("city", "曲靖市").put("district", "沾益区").getHome());
        check_region_id.put("530630", new YtJSONObject().put("new_region_id", "530681").put("province", "云南省").put("city", "昭通市").put("district", "水富市").getHome());
        check_region_id.put("540126", new YtJSONObject().put("new_region_id", "540104").put("province", "西藏自治区").put("city", "拉萨市").put("district", "达孜县").getHome());
        check_region_id.put("542421", new YtJSONObject().put("new_region_id", "540602").put("province", "西藏自治区").put("city", "那曲地区").put("district", "那曲县").getHome());
        check_region_id.put("610125", new YtJSONObject().put("new_region_id", "610118").put("province", "陕西省").put("city", "西安市").put("district", "户县").getHome());
        check_region_id.put("610427", new YtJSONObject().put("new_region_id", "610482").put("province", "陕西省").put("city", "咸阳市").put("district", "彬县").getHome());
        check_region_id.put("610721", new YtJSONObject().put("new_region_id", "610703").put("province", "陕西省").put("city", "汉中市").put("district", "南郑县").getHome());
        check_region_id.put("610821", new YtJSONObject().put("new_region_id", "610881").put("province", "陕西省").put("city", "榆林市").put("district", "神木县").getHome());
        check_region_id.put("620201", new YtJSONObject().put("new_region_id", "620200").put("province", "甘肃省").put("city", "嘉峪关市").put("district", "").getHome());
        check_region_id.put("620824", new YtJSONObject().put("new_region_id", "620881").put("province", "甘肃省").put("city", "平凉市").put("district", "华亭县").getHome());



        String sql = "";
        for (int i = 0; i < month.length; i++) {

            for (Map.Entry<String, JSONObject> s : check_region_id.entrySet()) {
                System.out.println("月份 : " + month[i]);
//                sql = "update dbadmin.o2o_region_category_app_tui_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where region_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_showlive_app_" + month[i] + " set regional_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where regional_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_tourism_idl_tui_" + month[i] + " set region_id_out = '" + s.getValue().getString("new_region_id") + "',province_out = '" + s.getValue().getString("province") + "',city_out = '" + s.getValue().getString("city") + "',district_out = '" + s.getValue().getString("district") + "' where region_id_out = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
//                sql = "update dbadmin.o2o_allplat_shop_bdl_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "' where region_id = '" + s.getKey() + "';";
//                System.out.println("更新 sql :" + sql);
//                update(sql);
                sql = "update dbadmin.o2o_bestsell_app_" + month[i] + " set region_id = '" + s.getValue().getString("new_region_id") + "',province = '" + s.getValue().getString("province") + "',city = '" + s.getValue().getString("city") + "',district = '" + s.getValue().getString("district") + "' where region_id = '" + s.getKey() + "';";
                System.out.println("更新 sql :" + sql);
                update(sql);
            }
        }

    }

    public static void select(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 打印 resultSet 数据
        ResultSetMetaData resultSetmd = resultSet.getMetaData();
        int columnsNumber = resultSetmd.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnsNumber; i++) {
                if (i > 1) System.out.print(",  ");
                String columnValue = resultSet.getString(i);
                System.out.print(columnValue + " " + resultSetmd.getColumnName(i));
            }
            System.out.println("");
        }
        preparedStatement.close();
    }

    public static void update(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();
        // 打印 resultSet 数据
        System.out.println("一共 : " + resultSet + " 条数据受影响 ! ");
        preparedStatement.close();

    }


}